package com.kandinfo.isbnmanage.repository.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import com.kandinfo.base.common.util.Constants;
import com.kandinfo.isbnmanage.domian.IsbnManage;
import com.kandinfo.isbnmanage.repository.IsbnAutoDistributeRepository;

@Repository
public class IsbnAutoDistributeRepositoryImpl implements IsbnAutoDistributeRepository{
	
	 @Autowired
	 private EntityManager entityManager;

	@SuppressWarnings("unchecked")
	@Override
	public Long getNoJobUserId(Long roleId, Integer isbnDistributeType,Long isbnManagerRoleId) {
		if (roleId == null || isbnDistributeType == null) {
			return null;
		}
		StringBuffer nativeSql = new StringBuffer();
        nativeSql.append(" SELECT ");
        nativeSql.append("    ur.user_id ");
        nativeSql.append(" FROM ");
        nativeSql.append("    user_role ur, ");
        nativeSql.append("    user u ");
        nativeSql.append(" WHERE ");
        nativeSql.append("    u.id = ur.user_id AND u.work_status = 0 ");
        nativeSql.append("		  AND u.status = '"+Constants.USER_ACTIVE+"' ");
        nativeSql.append("        AND ur.role_id = "+roleId+" ");
        if (isbnManagerRoleId != null) {
        	 nativeSql.append("  AND ur.user_id not in (   ");
        	 nativeSql.append("  select u1.id from  user u1,user_role ur1 where u1.id = ur1.user_id   ");
        	 nativeSql.append("        AND ur1.role_id = "+isbnManagerRoleId+" ");
        	 nativeSql.append("  )   ");
		}
        nativeSql.append("        AND ur.user_id not in ( SELECT  ");
        nativeSql.append("            id.distribute_to ");
        nativeSql.append("        FROM ");
        nativeSql.append("            isbn_distribute id ");
        nativeSql.append("        WHERE  ");
        nativeSql.append("             id.distribute_to = u.id ");
        nativeSql.append("                AND id.status = 0 ");
        nativeSql.append("                AND id.type = "+isbnDistributeType+") ");
        nativeSql.append(" LIMIT 1 ");
        
        Query nativeQuery = entityManager.createNativeQuery(nativeSql.toString());
		List<Long> result = nativeQuery.getResultList();
		
		return CollectionUtils.isEmpty(result)?null:Long.valueOf(result.get(0)+"");
	}

	@SuppressWarnings("unchecked")
	@Override
	public Long getHadMinJobUserId(Long roleId, Integer isbnDistributeType,Long isbnManagerRoleId) {
		if (roleId == null || isbnDistributeType == null || isbnManagerRoleId == null) {
			return null;
		}
		StringBuffer nativeSql = new StringBuffer();
        nativeSql.append(" SELECT ");
        nativeSql.append("    ur.user_id ");
        nativeSql.append(" FROM ");
        nativeSql.append("    user_role ur, ");
        nativeSql.append("    user u, ");
        nativeSql.append("    (SELECT  ");
        nativeSql.append("        id.distribute_to AS user_id, COUNT(id.id) AS count ");
        nativeSql.append("    FROM ");
        nativeSql.append("        isbn_distribute id ");
        nativeSql.append("    WHERE ");
        nativeSql.append("        id.status = 0 AND id.type = "+isbnDistributeType+" ");
        nativeSql.append("    GROUP BY id.distribute_to) a ");
        nativeSql.append(" WHERE ");
        nativeSql.append("    u.id = ur.user_id AND u.id = a.user_id ");
        nativeSql.append("        AND u.work_status = 0 ");
        nativeSql.append("		  AND u.status = '"+Constants.USER_ACTIVE+"' ");
        nativeSql.append("        AND ur.role_id = "+roleId+" ");
        if (isbnManagerRoleId != null) {
	       	 nativeSql.append("  AND ur.user_id not in (   ");
	       	 nativeSql.append("  	select u1.id from  user u1,user_role ur1 where u1.id = ur1.user_id   ");
	       	 nativeSql.append("        AND ur1.role_id = "+isbnManagerRoleId+" ");
	       	 nativeSql.append("  )   ");
		}
        nativeSql.append(" ORDER BY a.count ");
        nativeSql.append(" LIMIT 1 ");
        
        Query nativeQuery = entityManager.createNativeQuery(nativeSql.toString());
        List<Long> result = nativeQuery.getResultList();
        return CollectionUtils.isEmpty(result)?null:Long.valueOf(result.get(0)+"");
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Long> getExsitRefKey(int type, List<Long> refKeyList) {
		List<Long> result = new ArrayList<Long>();
		if (CollectionUtils.isEmpty(refKeyList)) {
			return result;
		}
		StringBuffer nativeSql = new StringBuffer();
        nativeSql.append(" SELECT ");
        nativeSql.append("    id.ref_key ");
        nativeSql.append(" FROM ");
        nativeSql.append("    isbn_distribute id ");
        nativeSql.append(" WHERE ");
        nativeSql.append("    id.type = "+type+" ");
        nativeSql.append("    AND id.ref_key IN (");
        for (int i = 0; i < refKeyList.size(); i++) {
			if (i != 0) {
				nativeSql.append(",");
			}
			nativeSql.append(refKeyList.get(i));
		}
        nativeSql.append(") ");
        Query nativeQuery = entityManager.createNativeQuery(nativeSql.toString());
        List<Long> list = nativeQuery.getResultList();
        for (int i = 0; i < list.size(); i++) {
        	result.add(Long.valueOf(list.get(i)+""));
		}
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<IsbnManage> getIsbnManageByInfoId(List<Long> refKeyList) {
		List<IsbnManage> result = new ArrayList<IsbnManage>();
		if (CollectionUtils.isEmpty(refKeyList)) {
			return result;
		}
		
		StringBuffer nativeSql = new StringBuffer();
        nativeSql.append(" SELECT ");
        nativeSql.append("    im.* ");
        nativeSql.append(" FROM ");
        nativeSql.append("    isbn_manage im, ");
        nativeSql.append("    isbn_information ii ");
        nativeSql.append(" WHERE ");
        nativeSql.append("     im.barcode = ii.barcode AND ii.id IN (");
        for (int i = 0; i < refKeyList.size(); i++) {
			if (i != 0) {
				nativeSql.append(",");
			}
			nativeSql.append(refKeyList.get(i));
		}
        nativeSql.append(") ");
		
        Query nativeQuery = entityManager.createNativeQuery(nativeSql.toString(),IsbnManage.class);
        return nativeQuery.getResultList();
	}
	 
	 
}
